import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileReader;
import java.io.PrintStream;
import java.sql.ResultSet;
import java.util.List;
import java.util.Vector;
import java.util.regex.Pattern;
import edu.columbia.ccls.GISDatabase;
import edu.columbia.ccls.JDBCDatabase;

/**
 * 
 */

/**
 * @author haimonti
 *
 */

public class GenerateKMLInfo
{
	class Record 
	{
		String label;
		String color;
		double lon;
		double lat;
		String rmks;

		Record(String label, String color, double lon, double lat, String rmks) 
		{
			this.label = label;
			this.color = color;
			//this.geoLat = geoLat;
			this.lon = lon;
			this.lat = lat;
			this.rmks = rmks;
		}
	}

	Placemark placemark;
	enum GISType {POLYGON, LINESTRING};
	String kmlString = "";

	//Initialize the folder name to manhole events MHE for now 
	String tType = "MHF";
	Vector<Record> data = new Vector<Record> ();

	String styleURL;

	String preamble = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
	"<kml xmlns=\"http://www.opengis.net/kml/2.2\">" +
	"<Document>" +
	"<name>%s</name>\n";

	String post = "</Document>" +
	"</kml>";

	String pointFormat = "<Placemark><name>%s</name><styleUrl>#%s</styleUrl> <ExtendedData><Data name=\"rmks\"><value>%s</value> </Data></ExtendedData> " + 
	"<Point><coordinates>%f,%f,0</coordinates></Point></Placemark>\n";

	Pattern p1 = Pattern.compile("<coordinates>");
	Pattern p2 = Pattern.compile("</coordinates>");

	//This is the constructor class
	public GenerateKMLInfo(String inputString, String trbType, Placemark placemark, List<String> getBorough) throws Exception
	{
		this.placemark = placemark;
		this.styleURL = 	"<Style id=\"%1$s%2$s\">" +
		"<IconStyle>" +
		"<scale>1.</scale>"+
		"<Icon>" +
		"<href>%1$s%2$s.png</href>"+	
		"</Icon>" +
		"<hotSpot x=\"16\" y=\"16\" xunits=\"pixels\" yunits=\"pixels\"/>"+
		"</IconStyle>" +
		" <BalloonStyle><text> <![CDATA[$[rmks]]]></text></BalloonStyle> </Style>\n";
		tType=trbType;
		String query;
		//Adjust the query based on the trouble type
		query = "select * from " ;
		if (tType.equalsIgnoreCase("Rank")) {
			query += "("+ getRankQuery(getBorough) ;

		}
		else {
			query += "("+ getTicketQuery(tType, getBorough) + ")";
			query += " as s";
		}

		query += " where ";
		query += queryPolygon(inputString, getBorough);
		//System.out.println("The query finally is"+ queryPoly);
		System.out.println("No of boroughs selected are -- from GenKML: "+getBorough.size());

		getKML(query, tType);

	}
	//Use the main method to test the kml file generated
	public static void main(String [] args) throws Exception
	{
		System.out.println("Test Main");
		String fileName = args[0];
		System.out.println("input file name is " + fileName);
		String poly = "";
		String tt="MHF";
		BufferedReader reader = new BufferedReader(new FileReader(fileName));//  ( new InputStreamReader(System.in));
		String line = "";
		while ((line = reader.readLine()) != null) 
		{	
			poly += line;
			//System.out.println("The line is "+poly);
		}
		//System.out.println(new GenerateKMLInfo(poly,tt, new Placemark()).kmlString + "\tGenerateKML.java @ 183", getBorough); //Debug
	}

	//Method that generates the kml file
	void getKML(String queryPoly, String title) throws Exception {
		try
		{
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			PrintStream stream = new PrintStream(baos);

			//Getting the database connections
			JDBCDatabase db = new GISDatabase();
			db.setUser("calm_user");
			db.setDatabaseName("coned");
			//db.setPort(5432);

			//KML Preamble - the beginning of the KML
			stream.printf(preamble, title);
			for (int i = 0; i < placemark.getSize() ; i++)
			{
				stream.printf(styleURL, this.placemark.getName(), "" + i);
			}
			//			stream.printf(preFolder, title);
			data = new Vector<Record> ();
			System.out.println("Query is: "+queryPoly);
			ResultSet rs = db.executeQuery(queryPoly);
			while (rs!=null && rs.next()) 
			{
				String ticket = rs.getString("label");
				String act = rs.getString("color");
				double x = rs.getDouble("lon");
				double y = rs.getDouble("lat");
				String remarks = rs.getString("rmks");
				data.add(new Record(ticket, act, x, y, remarks));
			}
			System.out.println("Data size: "+data.size());
			String icon=this.placemark.getName();
			System.out.println("The icon to be used is " + icon);

			for (Record row: data) 
			{
				String name = row.label;
				String act = row.color;
				stream.printf(pointFormat, name, icon+row.color,row.rmks,row.lon, row.lat); 
			}
			//			stream.println(postFolder);
			stream.println(post);
			kmlString = baos.toString();
			//db.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}

	public String getTicketQuery(String tType, List<String> getBorough){

		//// --- Jitter --- ////
		//		String query = "select ticket as label, lon, lat, ";
		//		query += " (case when (act similar to '%(MHF|MHO|MHX)') then '113'";
		//		query += "  when (act similar to '%(SMH)') then '95'";
		//		query += "  when (act similar to '%(ACB|WBR|FRQ)') then '82'";
		//		query += "  when (act similar to '%(DCB|UAC|UDC|FLT|LV|NL|NLA)') then '64'";
		//		query += "  when (act similar to '%(SOP|SOB|SO|ESR|ENE)') then '19'";
		//		query += " else '0'";
		//		query += " end) as color";
		//		query += " from nb2338.nb_jitter";
		//		query += " where act similar to \'%("+tType+")\'";
		//		return query;

		//		//// ---  Manhattan_Temp --- ////
		//		String query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
		//		query += " (case when (act similar to '%(MHF|MHO|MHX)') then '113'";
		//		query += "  when (act similar to '%(SMH)') then '95'";
		//		query += "  when (act similar to '%(ACB|WBR|FRQ)') then '82'";
		//		query += "  when (act similar to '%(DCB|UAC|UDC|FLT|LV|NL|NLA)') then '64'";
		//		query += "  when (act similar to '%(SOP|SOB|SO|ESR|ENE)') then '19'";
		//		query += " else '0'";
		//		query += " end) as color";
		//		query += " from haimonti.manhattan_temp";
		//		query += " where act similar to \'%("+tType+")\'";
		//		return query;

		//		//// ---  Three Color Scheme --- ////
		//		String query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
		//		query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
		//		query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
		//		query += " else '2'";
		//		query += " end) as color";
		//		query += " from haimonti.manhattan_temp";
		//		query += " where act similar to \'%("+tType+")\'";

		//// ---  Jitter + Three Color Scheme --- ////

		// extra trouble types for Bronx
		//ABF,DUP,EQF,EXW,FMH,NLB,NLC,NLS,SCD,SIP

		int boroughType=getBoroughType(getBorough);
		String query;
		switch(boroughType){


		case 1:// Only Manhattan
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks ";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Manhattan selected");
			break;

		case 2:// Only Brooklyn
			query =" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color,  modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Brooklyn selected");
			break;

		case 3: //Only Bronx
			query =" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color,  modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Bronx selected");
			break;

		case 4: //Only Queens
			query =" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color,  modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("queens selected");
			break;

		case 5://Only Brooklyn and Manhattan

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Brooklyn and manhattan boroughs selected");

			break;

		case 6://Only Bronx and Manhattan

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Bronx and manhattan boroughs selected");
			break;

		case 7: //Only Bronx and Brooklyn

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Bronx and Brooklyn boroughs selected");


			break;

		case 8://Only queens and Manhattan

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Queens and manhattan boroughs selected");

			break;


		case 9: //Only queens and Brooklyn

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("queens and Brooklyn boroughs selected");


			break;

		case 10: //Only Bronx and Queen

			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println("Bronx and Brooklyn boroughs selected");


			break;

		case 11://ME, BE, XE
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println(" ME, BE, XE boroughs selected");
			break;


		case 12://ME, BE, QE
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println(" ME, BE, QE boroughs selected");
			break;


		case 13://ME, QE, XE
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println(" ME, BE, XE boroughs selected");
			break;


		case 14://QE, BE, XE
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println(" QE, BE, XE boroughs selected");
			break;


		default://All four
			query =" select ticket as label, cast(lon as numeric) as lon, cast(lat as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from haimonti.jitterwithremarkstbl";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_BE ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_xe ";
			query += " where act similar to \'%("+tType+")\'";
			query += " union ";
			query +=" select ticket as label, cast(lon  as numeric) as lon, cast(lat  as numeric)  as lat, ";
			query += " (case when (act similar to '%(MHF|MHO|MHX|SMH)') then '1'";
			query += "  when (act similar to '%(ACB|DCB|WBR|NL|NLA|FLT|SOP|SO|SOB|UDC|UAC|LV|FRQ)') then '0'";
			query += " else '2'";
			query += " end) as color, modifiedrmks as rmks";
			query += " from axinia.jitterwithremarkstbl_qe ";
			query += " where act similar to \'%("+tType+")\'";
			System.out.println(" All the boroughs selected");
			break;

		}
		return query;
	}

	/**
	 * It still needs to be finished. Include table for Bronx.
	 * 
	 * @param getBorough
	 * @return
	 */

	public String getRankQuery(List<String> getBorough) {
		//		String rankQuery = "select label, lon, lat, color from nb2338.nb_rankviz2";
		//		String rankQuery = "select * from nb2338.nblt_viz"; // Becky's labeling task
		//		String rankQuery = "select node as label, lon, lat, ((degree*60)/(select max(degree) from nb2338.nw2_node)  + 40) as color from nb2338.nw2_node, nb2338.nw2_node_data where nw2_node.node like nw2_node_data.struct";
		//		String rankQuery = " select structureid||'_'||structureType as label,";
		//		rankQuery += " lon, lat, (rank*128)/(select max(rank) from nb2338.nb_rankviz) as color";
		//		rankQuery += " from nb2338.nb_rankviz where lat is not null";

		int boroughType=getBoroughType(getBorough);

		String rankQuery;

		switch(boroughType){

		case 1:// Only Manhattan
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" order by color ) as s";
			System.out.println("Manhattan selected for Rank ");
			break;

		case 2:// Only Brooklyn
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";
			System.out.println("Brooklyn selected for Rank ");
			break;


		case 3: //Only Bronx
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\" order by color ) as s";
			System.out.println("Bronx selected for Rank ");
			break;

		case 4: //Only Queens
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\" order by color ) as s";
			System.out.println("queens selected for Rank ");
			break;

		case 5://Only Brooklyn and Manhattan
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println(" Brooklyn and Manhattan boroughs selected for Rank ");
			break;

		case 6://Only Bronx and Manhattan
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\" order by color ) as s";

			System.out.println("Bronx and Manhattan boroughs selected for Rank ");
			break;

		case 7: //Only Bronx and Brooklyn
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println("Bronx and Brooklyn boroughs selected for Rank ");
			break;

		case 8://Only queens and Manhattan
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\" order by color ) as s";

			System.out.println("queens and Manhattan boroughs selected for Rank ");
			break;

		case 9: //Only Queens and Brooklyn
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println("Queens and Brooklyn boroughs selected for Rank ");
			break;

		case 10: //Only Bronx and Queens
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\" order by color ) as s";

			System.out.println("Bronx and Queens boroughs selected for Rank ");
			break;

		case 11://ME, BE, XE
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println("ME, BE, XE boroughs selected for Rank ");

			break;

		case 12://ME, BE, QE
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println("ME, BE, QE boroughs selected for Rank ");

			break;

		case 13://ME, QE, XE
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\" order by color ) as s";

			System.out.println("ME, QE, XE boroughs selected for Rank ");

			break;

		case 14://BE, QE, XE
			rankQuery = "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\"  "; 
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\" order by color ) as s";

			System.out.println("BE, QE, XE boroughs selected for Rank ");

			break;


		default://all
			rankQuery = "select (\"StructureNumber\" ||' '|| \"StructureType\") as label, \"lon\", \"lat\", cast(((\"RankInMachineLearning\"*63)/(select max(\"RankInMachineLearning\") from axinia.\"StructureRankingsFor2007_Phase3\")) as integer) as color,(\"StructureNumber\"||'-'||\"StructureType\") as rmks from axinia.\"StructureRankingsFor2007_Phase3\" ";
			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"BronxStructures_BXQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"BronxStructures_BXQ2_for_viz\"  ";

			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"QueensStructures_QEQ2_for_viz\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"QueensStructures_QEQ2_for_viz\"  ";

			rankQuery +=" UNION ";
			rankQuery += "select \"StructureNumber\"||' '||\"StructureType\" as label, \"StructureLongitude\" as lon,\"StructureLatitude\" as lat, cast(((\"RankInTargetingModel\"*63)/(select max(\"RankInTargetingModel\") from axinia.\"bad_list_Feb11\")) as integer) as color,\"StructureNumber\"||' '||\"StructureType\" as rmks  from axinia.\"bad_list_Feb11\" order by color ) as s";

			System.out.println("all of the boroughs selected for Rank ");

			break;

		}


		return rankQuery;
	}




	public String queryPolygon(String inputString, List<String> getBorough) throws Exception {
		String query = new String();

		String kmlcontent =inputString;
		ByteArrayInputStream inputStream = new ByteArrayInputStream(inputString.getBytes());
		int mark = 0;
		int c;
		while ((c = inputStream.read()) != -1){
			if (c == '<') {
				break;
			}
			mark++;
			inputStream.mark(mark);
		}
		inputStream.reset();
		if (c == -1 ) {
			System.out.println("Crashing!!!");
			return null; // we're returning null! isn't that a recipe for disaster?
		}
		//System.out.println("Before XMLReader--");
		XMLReader reader = new XMLReader(inputStream, kmlcontent);
		GISType type;
		String googleCoordinates = reader.getString("//edx:Polygon/edx:outerBoundaryIs/edx:LinearRing/edx:coordinates"); 
		if (!googleCoordinates.equals("")) 
		{
			query += " contains(polygonfromtext('POLYGON((";
			type = GISType.POLYGON;
		}
		else {
			googleCoordinates = reader.getString("//edx:LineString/edx:coordinates");

			if (! googleCoordinates.equals("")) {
				query += " distance(linestringfromtext('LINESTRING(";
				type = GISType.LINESTRING;
			}
			else {
				System.out.println("Throwing Exception Coordinates---");
				throw new Exception(inputString);
			}
		}
		System.out.println("The google coordinates  are --- "+ googleCoordinates);

		String [] points = googleCoordinates.split(" ");
		for (int p = 0 ; p < points.length; p++) {
			String [] coords = points[p].split(",");
			query += coords[0] + " " + coords[1];
			if (p < points.length -1) {
				query += ", ";
			}
		}

		if (type == GISType.POLYGON) {
			query += "))', 4326), pointfromtext('POINT(' || lon || '  ' || lat || ')',  4326))" ;
		}
		else if (type == GISType.LINESTRING) {
			query += ")', 4326), pointfromtext('POINT(' || lon || '  ' || lat || ')',  4326)) < .0002";
		}
		return query;
	}




	/**
	 * use it when you add queens
	 * @param getBorough
	 * @return
	 */
	private int getBoroughType(List<String> getBorough) {

		int boroughType=0;

		if(getBorough.size()>3||getBorough.size()==0){
			//Consider all of the boroughs
			boroughType=0;
		}
		else if(getBorough.size()==1){
			// only one borough
			String borough=getBorough.get(0);
			if(borough.equalsIgnoreCase("ME")){
				boroughType=1;
			}
			else if(borough.equalsIgnoreCase("BE")){
				boroughType=2;
			}
			else if(borough.equalsIgnoreCase("XE")){
				boroughType=3;
			}
			else if(borough.equalsIgnoreCase("QE")){
				boroughType=4;
			}
			else{
				boroughType=0;
			}
		}
		else if(getBorough.size()==2){
			// only one borough

			if(getBorough.contains("ME") && getBorough.contains("BE")){
				boroughType=5;
			}
			else if(getBorough.contains("ME") && getBorough.contains("XE")){
				boroughType=6;
			}
			else if(getBorough.contains("XE") && getBorough.contains("BE")){
				boroughType=7;
			}
			else if(getBorough.contains("ME") && getBorough.contains("QE")){
				boroughType=8;
			}
			else if(getBorough.contains("QE") && getBorough.contains("BE")){
				boroughType=9;
			}
			else if(getBorough.contains("XE") && getBorough.contains("QE")){
				boroughType=10;
			}
			else{
				boroughType=0;
			}
		}
		else if(getBorough.size()==3){
			if(getBorough.contains("ME")&& getBorough.contains("BE") &&  getBorough.contains("XE")){
				boroughType=11;
			}
			else if(getBorough.contains("ME")&& getBorough.contains("QE") && getBorough.contains("BE")){
				boroughType=12;
			}
			else if(getBorough.contains("ME")&& getBorough.contains("QE") && getBorough.contains("XE")){
				boroughType=13;
			}
			else if(getBorough.contains("XE")&& getBorough.contains("QE") && getBorough.contains("BE")){
				boroughType=14;
			}
			else{
				boroughType=0;
			}
		}


		return boroughType;
	}



}
